package com.lm.dao.impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.SuggestDAO;
import com.lm.model.Suggest;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;
import com.util.DateUtil;

public class SuggestDAOImpl implements SuggestDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List getSuggestListByUserID(int userID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_suggest  ");
		sb.append(" where userID= ").append(userID);
		sb.append(" order by suggestTime desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Suggest suggest = new Suggest();
				suggest.setContent(BlobToString((Blob) rs.getBlob("Content")));
				suggest.setId(rs.getInt("id"));
				suggest.setIsShow(rs.getInt("isShow"));
				suggest.setSuggestTime(rs.getDate("suggestTime"));
				suggest.setTypeId(rs.getInt("typeID"));
				suggest.setUserId(rs.getInt("userID"));
				suggest.setSuggestName(rs.getString("suggestName"));
				resList.add(suggest);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List getAllSuggest() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" select * from lm_suggest  ");
		sb.append(" order by suggestTime desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Suggest suggest = new Suggest();
				suggest.setContent(BlobToString((Blob) rs.getBlob("Content")));
				suggest.setId(rs.getInt("id"));
				suggest.setIsShow(rs.getInt("isShow"));
				suggest.setSuggestTime(rs.getDate("suggestTime"));
				suggest.setTypeId(rs.getInt("typeID"));
				suggest.setUserId(rs.getInt("userID"));
				suggest.setSuggestName(rs.getString("suggestName"));
				resList.add(suggest);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return resList;
	}

	public List getAllShowedSuggest() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_suggest  ");
		sb.append(" where isShow=1 order by suggestTime desc");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Suggest suggest = new Suggest();
				suggest.setContent(BlobToString((Blob) rs.getBlob("Content")));
				suggest.setId(rs.getInt("id"));
				suggest.setIsShow(rs.getInt("isShow"));
				suggest.setSuggestTime(rs.getDate("suggestTime"));
				suggest.setTypeId(rs.getInt("typeID"));
				suggest.setUserId(rs.getInt("userID"));
				suggest.setSuggestName(rs.getString("suggestName"));
				resList.add(suggest);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public Suggest getSuggestByUserIDAndSuggestID(int userID, String suggestID)
			throws Throwable {
		Suggest suggest = new Suggest();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_suggest  ");
		sb.append(" where userID= ").append(userID);
		sb.append(" and id=").append(Integer.parseInt(suggestID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				suggest.setContent(BlobToString((Blob) rs.getBlob("Content")));
				suggest.setId(rs.getInt("id"));
				suggest.setIsShow(rs.getInt("isShow"));
				suggest.setSuggestTime(rs.getDate("suggestTime"));
				suggest.setTypeId(rs.getInt("typeID"));
				suggest.setUserId(rs.getInt("userID"));
				suggest.setSuggestName(rs.getString("suggestName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return suggest;
	}

	public Suggest getSuggestBySuggestID(String suggestID) throws Throwable {
		Suggest suggest = new Suggest();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_suggest  ");
		sb.append(" where id=").append(Integer.parseInt(suggestID));
		sb.append(" and isShow=1");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				suggest.setContent(BlobToString((Blob) rs.getBlob("Content")));
				suggest.setId(rs.getInt("id"));
				suggest.setIsShow(rs.getInt("isShow"));
				suggest.setSuggestTime(rs.getDate("suggestTime"));
				suggest.setTypeId(rs.getInt("typeID"));
				suggest.setUserId(rs.getInt("userID"));
				suggest.setSuggestName(rs.getString("suggestName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return suggest;
	}

	private String BlobToString(Blob blob) throws SQLException {
		byte[] bdata = blob.getBytes(1, (int) blob.length());
		String text = new String(bdata);

		return text;
	}

	public void addSuggestByUserID(Suggest suggest, int userID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String currentDate = DateUtil.getCurrentDateString();

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_suggest(userID,Content,typeID,");
		sb.append(" suggestTime,suggestName)");
		sb.append(" values(").append(userID).append(",");
		// sb.append("'").append(content).append("',");
		sb.append("'").append(suggest.getContent()).append("',");
		sb.append(suggest.getTypeId()).append(",");
		sb.append("'").append(currentDate).append("',");
		sb.append("'").append(suggest.getSuggestName()).append("')");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editSuggestByUserIDAndSuggestID(Suggest suggest, int userID,
			String suggestID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String currentDate = DateUtil.getCurrentDateString();

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_suggest set Content='").append(
				suggest.getContent());
		sb.append("',suggestTime='").append(currentDate);
		sb.append("',typeID=").append(suggest.getTypeId());
		sb.append(",suggestName='").append(suggest.getSuggestName())
				.append("'");
		sb.append(" where userID=").append(userID);
		sb.append(" and  id=").append(Integer.parseInt(suggestID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSuggestBySuggestID(int suggestID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_suggest ");
		sb.append(" where id=").append(suggestID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
